package org.eclipse.epsilon.emc.rdbms.core.classmap;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import org.eclipse.epsilon.emc.rdbms.core.imodel.DBReflexiveModelElement;
import org.eclipse.epsilon.emc.rdbms.core.persistancebroker.PersistanceBroker;
import org.eclipse.epsilon.emc.rdbms.core.persistancemechanism.RelationalDatabase;
import org.eclipse.epsilon.emc.rdbms.core.sqlstatement.DeleteSQLStatement;
import org.eclipse.epsilon.emc.rdbms.core.sqlstatement.InsertSQLStatement;
import org.eclipse.epsilon.emc.rdbms.core.sqlstatement.SQLStatement;
import org.eclipse.epsilon.emc.rdbms.core.sqlstatement.SelectSQLStatement;
import org.eclipse.epsilon.emc.rdbms.core.sqlstatement.SelectWithJoins;
import org.eclipse.epsilon.emc.rdbms.core.sqlstatement.UpdateSQLStatement;

public class ClassMap 
{

/**
* This  gets the statement that will need to be passed back in order to be executed.
*  
* @param  element  This is the DBReflexiveModelElement that is to be inserted into the database
* @param  HashMap  This is the properties for the insert statement for example providing column name and value will override the default specified in the Type Mapper.
* @return The SQL Statement that is to be executed in the form of INSERT INTO <table> (<col1>,<col2>,<col3>) VALUES (<'VAL1'>,<'VAL2'>,<'VAL3'>)
*/
	
public String getInsertSQLFor(DBReflexiveModelElement element,HashMap properties) throws SQLException
{
RelationalDatabase connection = PersistanceBroker.connectTo();	          //So we have the connection to the database from the Persistance Broker.
SQLStatement QueryBuilder = new InsertSQLStatement();
String statement = QueryBuilder.BuildForCriteria(connection.getSQLTables(element.getType()),connection.getSQLColumns(element.getType()),connection.getPrimaryKey(element.getType()),connection.getSQLColumns(element.getType()),properties);
return statement;
}


/**
* This  gets the statement that will need to be passed back in order to be executed.
*  
* @param  element  This is the DBReflexiveModelElement that is to be updated in the database
* @param  HashMap  This is the properties for the update statement for example providing column name and value mean that the column is set to that specific value for the element.
* @return The SQL Statement that is to be executed in the form of UPDATE <table> SET <col1> = <val1> WHERE <tablePK>  = <elementID>;
*/
public String getUpdateSQLFor(DBReflexiveModelElement element,HashMap properties) throws SQLException
{
	RelationalDatabase connection = PersistanceBroker.connectTo();	          
	SQLStatement QueryBuilder = new UpdateSQLStatement();	
	String statement = QueryBuilder.BuildForCriteria(connection.getSQLTables(element.getType()), properties, element.getProps());
	return statement;
}

/**
* This  gets the statement that will need to be passed back in order to be executed.
*  
* @param  element  This is the DBReflexiveModelElement that is to be deleted from the database
* @return The SQL Statement that is to be executed in the form of DELETE FROM <table> where <tablePK> = <elementID>
*/
public String getDeleteSQLFor(DBReflexiveModelElement element) throws SQLException
{
	RelationalDatabase connection = PersistanceBroker.connectTo();	          
	SQLStatement QueryBuilder = new DeleteSQLStatement();	
	String statement = QueryBuilder.BuildForCriteria(connection.getSQLTables(element.getType()), element.getProps());
	return statement;
}

/**
* This  gets the statement that will need to be passed back in order to be executed.
*  
* @param  element  This is the DBReflexiveModelElement that is to be deleted from the database
* @return The SQL Statement that is to be executed in the form of DELETE FROM <table> where <tablePK> = <elementID>
*/
public String getSelectSQLFor(DBReflexiveModelElement element,ArrayList<String> properties) throws SQLException
{
	RelationalDatabase connection = PersistanceBroker.connectTo();	          
	SQLStatement QueryBuilder = new SelectSQLStatement();	
	String statement = QueryBuilder.BuildForCriteria(connection.getSQLTables(element.getType()),properties,element.getProps());
	return statement;
}

/**
* This  gets the statement that will need to be passed back in order to be executed.
*  
* @param  type  This is the Object type\tablename
* @param  properties This is the columns that you wish to return
* @param  criteria This is the criteria that will be specified in database terms what will be in the where clause.
* @return The SQL Statement that is to be executed in the form of DELETE FROM <table> where <tablePK> = <elementID>
*/
public String getSelectSQLForType(String type,ArrayList<String> properties,HashMap<String, Object> criteria ) throws SQLException
{
	RelationalDatabase connection = PersistanceBroker.connectTo();	          
	SQLStatement QueryBuilder = new SelectSQLStatement();	
	String statement = QueryBuilder.BuildForCriteria(connection.getSQLTables(type),properties,criteria);
	return statement;
}

/**
* This  gets the statement that will need to be passed back in order to be executed.
*  
* @param  element  This is the element\row that you wish to select
* @param  type This is the type\table you wish to select from 
* @param  PKcols This is a list of columns that are used as the Primary Key for the table
* @param PKTables This is the list of tables that are used in the join
* @param FKcols   This is the list of columns to be used from the foreign Key table
* @param FKtables This is the list of Foreign Key tables used in the join
* @return The SQL Statement that is to be executed in the form of DELETE FROM <table> where <tablePK> = <elementID>
*/
public String getSelectWithJoinsFor(DBReflexiveModelElement element,String type,ArrayList<String> PKcols,ArrayList<String> PKtables,ArrayList<String> FKcols, ArrayList<?> FKtables) throws SQLException 
{
	RelationalDatabase connection = PersistanceBroker.connectTo();	      
	SQLStatement QueryBuilder = new SelectWithJoins();
	//Get the column information for the two tables that we need to join on.
	ArrayList<String> columns = new ArrayList<String>();
	String[] tablestr = PKtables.get(0).toString().split("\\.");

	
	ResultSet columnsPrimary = connection.getPrimaryKey(tablestr[2].toString().toUpperCase());
	while (columnsPrimary.next())
	{
		columns.add("fktable."+columnsPrimary.getString(4));
	}
	//columns.add(FKcols.get(0));
	//ResultSet columnsForeign = connection.getSQLColumns(type);
	
	//while (columnsPrimary.next())
	//{
	//columns.add("pktable"+"."+columnsPrimary.getString("COLUMN_NAME"));
	 //columns.add(columnsPrimary.getString("TABLE_CAT")+"."+columnsPrimary.getString("TABLE_SCHEM")+"."+columnsPrimary.getString("TABLE_NAME")+"."+columnsPrimary.getString("COLUMN_NAME"));
	//}
	//while (columnsForeign.next())
	//{
	//	columns.add("fktable"+"."+columnsForeign.getString("COLUMN_NAME"));
	 //columns.add(columnsForeign.getString("TABLE_CAT")+"."+columnsForeign.getString("TABLE_SCHEM")+"."+columnsForeign.getString("TABLE_NAME")+"."+columnsForeign.getString("COLUMN_NAME"));
	//}
	System.out.println("You are here");
	
	String statement = QueryBuilder.BuildForCriteria(connection.getSQLTables(element.getType()), columns, PKtables, FKtables, PKcols, FKcols, element.getProps());
	return statement;
 }

public String getSelectWithJoinsForType(String PrimaryKeytype,String ForeignKeytype, ArrayList<String> PKcols,ArrayList<String> PKtables,ArrayList<String> FKcols, ArrayList<?> FKtables) throws SQLException 
{
	
	RelationalDatabase connection = PersistanceBroker.connectTo();	      
	SQLStatement QueryBuilder = new SelectWithJoins();
	//Get the column information for the two tables that we need to join on.
	ArrayList<String> columns = new ArrayList<String>();
	
	ResultSet columnsPrimary = connection.getSQLColumns(ForeignKeytype);
	ResultSet columnsForeign = connection.getSQLColumns(PrimaryKeytype);
    	
	while (columnsPrimary.next())
	{
		columns.add("pktable"+"."+columnsPrimary.getString("COLUMN_NAME"));
	 columns.add(columnsPrimary.getString("TABLE_CAT")+"."+columnsPrimary.getString("TABLE_SCHEM")+"."+columnsPrimary.getString("TABLE_NAME")+"."+columnsPrimary.getString("COLUMN_NAME"));
	}
	while (columnsForeign.next())
	{
	  columns.add("fktable"+"."+columnsForeign.getString("COLUMN_NAME"));
	 columns.add(columnsForeign.getString("TABLE_CAT")+"."+columnsForeign.getString("TABLE_SCHEM")+"."+columnsForeign.getString("TABLE_NAME")+"."+columnsForeign.getString("COLUMN_NAME"));
	}
	
	String statement = QueryBuilder.BuildForCriteria(connection.getSQLTables(PrimaryKeytype), columns, PKtables, FKtables, PKcols, FKcols, new HashMap());
	return statement;
 }





/**
* This  gets the statement that will need to be passed back in order to be executed. This will be the type\ table so for exampe
* This will be called when we want to Update multiple rows in an existing table.
*  
* @param  element  This is the element\row that you wish to select
* @param  type This is the type\table you wish to select from 
* @param  PKcols This is a list of columns that are used as the Primary Key for the table
* @param PKTables This is the list of tables that are used in the join
* @param FKcols   This is the list of columns to be used from the foreign Key table
* @param FKtables This is the list of Foreign Key tables used in the join
* @return The SQL Statement that is to be executed in the form of DELETE FROM <table> where <tablePK> = <elementID>
*/

public String getUpdateSQLForType(String type, HashMap properties) throws SQLException 
{
	RelationalDatabase connection = PersistanceBroker.connectTo();	          
	SQLStatement QueryBuilder = new UpdateSQLStatement();	
	String statement = QueryBuilder.BuildForCriteria(connection.getSQLTables(type), properties, new HashMap());
	return statement;
}

/**
* This  gets the statement that will need to be passed back in order to be executed. This will be the type\ table so for example
* This will be called when we want to Delete multiple all rows in an existing table.
*  
* @param  type This is the type\table you wish to select from 
* @return The SQL Statement that is to be executed in the form of DELETE FROM <table> where <tablePK> = <elementID>
*/

public String getDeleteSQLForType(String type) throws SQLException
{
	RelationalDatabase connection = PersistanceBroker.connectTo();	          
	SQLStatement QueryBuilder = new DeleteSQLStatement();	
	String statement = QueryBuilder.BuildForCriteria(connection.getSQLTables(type), null);
	return statement;
}	
		
}



		

